﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

public partial class AddDocument : System.Web.UI.Page
{
    private SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.IsAuthenticated == false)
        {
            Response.Redirect("login.aspx");
        }

        if (Request.IsAuthenticated)
        {
            string n = "";
            SqlCommand sCmd = new SqlCommand("SELECT name FROM USERS WHERE id=@id", conn);
            sCmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = User.Identity.Name;
            conn.Open();
            SqlDataReader dr = sCmd.ExecuteReader();
            while (dr.Read())
            {
                string name = Convert.ToString(dr[0].ToString());
                n = name;
            }
            dr.Close();
            conn.Close();

            WelcomeBackMessage.Text = "Welcome back, " + n + "!";

            AuthenticatedHeaderPanel.Visible = true;
            AuthenticatedMessagePanel.Visible = true;
            AuthenticatedMenuPanel.Visible = true;

            if (!IsPostBack)
            {
                SetData();
            }
        }
        else
        {
            AuthenticatedHeaderPanel.Visible = false;
            AuthenticatedMessagePanel.Visible = false;
            AuthenticatedMenuPanel.Visible = false;
        }
    }

    private void SetData()
    {
        List<int> document_id_company = new List<int>();
        List<int> document_id_client = new List<int>();

        int id_user = Convert.ToInt32(this.User.Identity.Name);

        //COMPANY
        conn.Open();
        SqlCommand selCmd =
            new SqlCommand("SELECT id_company FROM user_company WHERE id_user=@id_user AND client=@client", conn);
        selCmd.Parameters.Add(new SqlParameter("@id_user", System.Data.SqlDbType.Int)).Value = id_user;
        selCmd.Parameters.Add(new SqlParameter("@client", System.Data.SqlDbType.VarChar)).Value = "false";
        SqlDataReader rd = selCmd.ExecuteReader();
        while (rd.Read())
        {
            document_id_company.Add(Convert.ToInt32(rd["id_company"]));
        }
        rd.Close();
        conn.Close();
        foreach (int id_company in document_id_company)
        {
            conn.Open();
            SqlCommand selCmd2 = new SqlCommand("SELECT id, company_name FROM company WHERE id=@id", conn);
            selCmd2.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.Int)).Value = id_company;
            SqlDataReader rd1 = selCmd2.ExecuteReader();
            while (rd1.Read())
            {
                ListItem item = new ListItem();
                item.Value = rd1["id"].ToString();
                item.Text = rd1["company_name"].ToString();
                Drop_company_from.Items.Add(item);
            }
            rd1.Close();
            conn.Close();
        }

        //CLIENT
        conn.Open();
        SqlCommand selCmd_client =
            new SqlCommand("SELECT id_company FROM user_company WHERE id_user=@id_user AND client=@client", conn);
        selCmd_client.Parameters.Add(new SqlParameter("@id_user", System.Data.SqlDbType.Int)).Value = id_user;
        selCmd_client.Parameters.Add(new SqlParameter("@client", System.Data.SqlDbType.VarChar)).Value = "true";
        SqlDataReader rdd = selCmd_client.ExecuteReader();
        while (rdd.Read())
        {
            document_id_client.Add(Convert.ToInt32(rdd["id_company"]));
        }
        rd.Close();
        conn.Close();
        foreach (int id_company in document_id_client)
        {
            conn.Open();
            SqlCommand selCmd_client1 = new SqlCommand("SELECT id, company_name FROM company WHERE id=@id", conn);
            selCmd_client1.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.Int)).Value = id_company;
            SqlDataReader rddd = selCmd_client1.ExecuteReader();
            while (rddd.Read())
            {
                ListItem item = new ListItem();
                item.Value = rddd["id"].ToString();
                item.Text = rddd["company_name"].ToString();
                Drop_client_to.Items.Add(item);
            }
            rddd.Close();
            conn.Close();
        }
        Drop_company_from.Items.Insert(0, "--Select--");
        Drop_address_company_from.Items.Insert(0, "--Select--");
        Drop_BANK_company_from.Items.Insert(0, "--Select--");
        Drop_ACCOUNT_company_from.Items.Insert(0, "--Select--");

        Drop_client_to.Items.Insert(0, "--Select--");
        Drop_address_client_to.Items.Insert(0, "--Select--");
        Drop_BANK_client_to.Items.Insert(0, "--Select--");
        Drop_ACCOUNT_client_to.Items.Insert(0, "--Select--");

        //DOCUMENT TYPE
        conn.Open();
        SqlCommand sel_document_type = new SqlCommand("SELECT id, type_name FROM document_type", conn);
        SqlDataReader rd2 = sel_document_type.ExecuteReader();
        while (rd2.Read())
        {
            ListItem item = new ListItem();
            item.Value = rd2["id"].ToString();
            item.Text = rd2["type_name"].ToString();
            Drop_doc_type.Items.Add(item);
        }
        rd2.Close();
        conn.Close();
        Drop_doc_type.Items.Insert(0, "--Select--");

        //PAYMENT TYPE
        conn.Open();
        SqlCommand sel_payment_type = new SqlCommand("SELECT id, type_name FROM payment_type", conn);
        SqlDataReader rd3 = sel_payment_type.ExecuteReader();
        while (rd3.Read())
        {
            ListItem item = new ListItem();
            item.Value = rd3["id"].ToString();
            item.Text = rd3["type_name"].ToString();
            Drop_pay_type.Items.Add(item);
        }
        rd3.Close();
        conn.Close();
        Drop_pay_type.Items.Insert(0, "--Select--");

        //id_user kurš lietotājs šobrīd ir ielogojies
        int id_main_user = Convert.ToInt32(User.Identity.Name);
        lbl_user_id.Text = id_main_user.ToString();

        //_User.EditUser(lbl_user_id.Text);
        //lbl_user.Text = _User.EditUser(lbl_user_id.Text)._Name + " " + _User.EditUser(lbl_user_id.Text)._SecondName;
    }

    protected void AddButton_Click(object sender, EventArgs e)
    {
        string doc_numb = tb_doc_number.Text;
        string doc_date = tb_doc_date.Text;
        string doc_pay_date = tb_doc_pay_date.Text;
        string id_isu_user = lbl_user_id.Text;
        string id_company_by = Drop_company_from.SelectedItem.Value;
        string id_client_to = Drop_client_to.SelectedItem.Value;
        string id_document_type = Drop_doc_type.SelectedItem.Value;
        string id_payment_type = Drop_pay_type.SelectedItem.Value;
        string delivery_place = tb_delivery_place.Text;
        string address_company_by = Drop_address_company_from.SelectedItem.Value;
        string address_client_to = Drop_address_client_to.SelectedItem.Value;
        string bank_company_by = Drop_BANK_company_from.SelectedItem.Value;
        string bank_client_to = Drop_BANK_client_to.SelectedItem.Value;
        string account_company_by = Drop_ACCOUNT_company_from.SelectedItem.Value;
        string account_client_to = Drop_ACCOUNT_client_to.SelectedItem.Value;
        string statuss = "Active";

        _Invoice.Insert(doc_numb, doc_date, doc_pay_date, id_isu_user, id_company_by, id_client_to,
                        id_document_type, id_payment_type, delivery_place, address_company_by, address_client_to,
                        bank_company_by, bank_client_to, account_company_by, account_client_to, statuss);

        SqlCommand SelCmd_MAX = new SqlCommand("SELECT MAX(id) FROM document", conn);
        conn.Open();
        string document_id = Convert.ToString(SelCmd_MAX.ExecuteScalar());
        conn.Close();
        Session["Doc_ID"] = document_id;

        Response.Redirect("EditDocment.aspx");
    }

    protected void CancelButton_Click(object sender, EventArgs e)
    {
        Response.Redirect("MyInvoices.aspx");
    }

    protected void Drop_company_from_SelectedIndexChanged1(object sender, EventArgs e)
    {
        if (Drop_company_from.SelectedItem.Value == "--Select--")
        {
            Drop_address_company_from.Items.Clear();
            Drop_address_company_from.Items.Insert(0, "--Select--");
            Drop_BANK_company_from.Items.Clear();
            Drop_BANK_company_from.Items.Insert(0, "--Select--");
            Drop_ACCOUNT_company_from.Items.Clear();
            Drop_ACCOUNT_company_from.Items.Insert(0, "--Select--");
        }
        else
        {
            Drop_address_company_from.Items.Clear();
            int compID = Convert.ToInt32(Drop_company_from.SelectedItem.Value);
            FillAddress_CompanyFrom(compID);

            Drop_BANK_company_from.Items.Clear();
            int compID1 = Convert.ToInt32(Drop_company_from.SelectedItem.Value);
            FillBanks_CompanyFrom(compID1);
        }
    }

    protected void Drop_BANK_company_from_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (Drop_BANK_company_from.SelectedItem.Value == "--Select--")
        {
            Drop_ACCOUNT_company_from.Items.Clear();
            Drop_ACCOUNT_company_from.Items.Insert(0, "--Select--");
        }
        else
        {
            Drop_ACCOUNT_company_from.Items.Clear();
            int bankID = Convert.ToInt32(Drop_BANK_company_from.SelectedItem.Value);
            int compID1 = Convert.ToInt32(Drop_company_from.SelectedItem.Value);
            FillAccounts_CompanyFrom(bankID, compID1);
        }
    }

    private void FillAddress_CompanyFrom(int compID)
    {
        conn.Open();
        SqlCommand sel_company_by_address =new SqlCommand("SELECT id, address FROM company_address WHERE id_company=@company_id", conn);
        sel_company_by_address.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = compID;
        SqlDataReader rd = sel_company_by_address.ExecuteReader();
        while (rd.Read())
        {
            ListItem item = new ListItem();
            item.Value = rd["id"].ToString();
            item.Text = rd["address"].ToString();
            Drop_address_company_from.Items.Add(item);
        }
        rd.Close();
        Drop_address_company_from.Items.Insert(0, "--Select--");
        conn.Close();
    }
    private void FillBanks_CompanyFrom(int compID1)
    {
        List<int> bank = new List<int>();
        List<int> company_bank = new List<int>();
        conn.Open();
        SqlCommand sel_company_by_bank =
            new SqlCommand("SELECT id_bank FROM company_bank WHERE id_company=@company_id GROUP BY id_bank", conn);
        sel_company_by_bank.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = compID1;
        SqlDataReader rd = sel_company_by_bank.ExecuteReader();
        while (rd.Read())
        {
            bank.Add(Convert.ToInt32(rd["id_bank"]));
        }
        rd.Close();
        conn.Close();

        conn.Open();
        foreach (var bank_id in bank)
        {
            SqlCommand selCmd = new SqlCommand(
                "SELECT id, bank_name FROM bank WHERE id=@bank_id GROUP BY id, bank_name", conn);
            selCmd.Parameters.Add(new SqlParameter("@bank_id", System.Data.SqlDbType.Int)).Value = bank_id;
            SqlDataReader rd1 = selCmd.ExecuteReader();
            while (rd1.Read())
            {
                ListItem item = new ListItem();
                item.Value = rd1["id"].ToString();
                item.Text = rd1["bank_name"].ToString();
                Drop_BANK_company_from.Items.Add(item);
            }
            rd1.Close();
        }
        Drop_BANK_company_from.Items.Insert(0, "--Select--");
        conn.Close();
    }
    private void FillAccounts_CompanyFrom(int bankID, int compID)
    {
        List<int> company_bank_ID = new List<int>();
        conn.Open();
        SqlCommand sel_company_by_bank_ID =
            new SqlCommand("SELECT id FROM company_bank WHERE id_bank=@bank_id AND id_company=@company_id", conn);
        sel_company_by_bank_ID.Parameters.Add(new SqlParameter("@bank_id", System.Data.SqlDbType.Int)).Value = bankID;
        sel_company_by_bank_ID.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = compID;
        SqlDataReader rd = sel_company_by_bank_ID.ExecuteReader();
        while (rd.Read())
        {
            company_bank_ID.Add(Convert.ToInt32(rd["id"]));
        }
        conn.Close();
        foreach (var com_bank_ID in company_bank_ID)
        {
            conn.Open();
            SqlCommand sel_company_by_bank_account =
                new SqlCommand(
                    "SELECT id_account_number, account_number FROM account_number WHERE id_company_bank=@company_bank_id",
                    conn);
            sel_company_by_bank_account.Parameters.Add(new SqlParameter("@company_bank_id", System.Data.SqlDbType.Int)).
                Value = com_bank_ID;
            SqlDataReader rd1 = sel_company_by_bank_account.ExecuteReader();
            while (rd1.Read())
            {
                ListItem item = new ListItem();
                item.Value = rd1["id_account_number"].ToString();
                item.Text = rd1["account_number"].ToString();
                Drop_ACCOUNT_company_from.Items.Add(item);
            }
            conn.Close();
        }
        Drop_ACCOUNT_company_from.Items.Insert(0, "--Select--");
    }

    protected void Drop_client_to_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (Drop_client_to.SelectedItem.Value == "--Select--")
        {
            Drop_address_client_to.Items.Clear();
            Drop_address_client_to.Items.Insert(0, "--Select--");
            Drop_BANK_client_to.Items.Clear();
            Drop_BANK_client_to.Items.Insert(0, "--Select--");
            Drop_ACCOUNT_client_to.Items.Clear();
            Drop_ACCOUNT_client_to.Items.Insert(0, "--Select--");
        }
        else
        {
            Drop_address_client_to.Items.Clear();
            int clientID = Convert.ToInt32(Drop_client_to.SelectedItem.Value);
            FillAddress_ClientTo(clientID);
            Drop_BANK_client_to.Items.Clear();
            int clientID1 = Convert.ToInt32(Drop_client_to.SelectedItem.Value);
            FillBanks_ClientTo(clientID1);
        }
    }

    protected void Drop_BANK_client_to_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (Drop_BANK_client_to.SelectedItem.Value == "--Select--")
        {
            Drop_ACCOUNT_client_to.Items.Clear();
            Drop_ACCOUNT_client_to.Items.Insert(0, "--Select--");
        }
        else
        {
            Drop_ACCOUNT_client_to.Items.Clear();
            int client_bankID = Convert.ToInt32(Drop_BANK_client_to.SelectedItem.Value);
            int clientID1 = Convert.ToInt32(Drop_client_to.SelectedItem.Value);
            FillAccounts_ClientTo(client_bankID, clientID1);
        }
    }

    private void FillAddress_ClientTo(int clientID)
    {
        conn.Open();
        SqlCommand sel_client_to_address =
            new SqlCommand("SELECT id, address FROM company_address WHERE id_company=@company_id", conn);
        sel_client_to_address.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value =
            clientID;
        SqlDataReader rd = sel_client_to_address.ExecuteReader();
        while (rd.Read())
        {
            ListItem item = new ListItem();
            item.Value = rd["id"].ToString();
            item.Text = rd["address"].ToString();
            Drop_address_client_to.Items.Add(item);
        }
        rd.Close();
        Drop_address_client_to.Items.Insert(0, "--Select--");
        conn.Close();
    }
    private void FillBanks_ClientTo(int clientID1)
    {
        List<int> bank = new List<int>();
        conn.Open();
        SqlCommand sel_client_to_bank =
            new SqlCommand("SELECT id_bank FROM company_bank WHERE id_company=@company_id GROUP BY id_bank", conn);
        sel_client_to_bank.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = clientID1;
        SqlDataReader rd = sel_client_to_bank.ExecuteReader();
        while (rd.Read())
        {
            bank.Add(Convert.ToInt32(rd["id_bank"]));
        }
        rd.Close();
        conn.Close();
        conn.Open();
        foreach (var bank_id in bank)
        {
            SqlCommand selCmd = new SqlCommand(
                "SELECT id, bank_name FROM bank WHERE id=@bank_id GROUP BY id, bank_name", conn);
            selCmd.Parameters.Add(new SqlParameter("@bank_id", System.Data.SqlDbType.Int)).Value = bank_id;
            SqlDataReader rd1 = selCmd.ExecuteReader();
            while (rd1.Read())
            {
                ListItem item = new ListItem();
                item.Value = rd1["id"].ToString();
                item.Text = rd1["bank_name"].ToString();
                Drop_BANK_client_to.Items.Add(item);
            }
            rd1.Close();
        }
        Drop_BANK_client_to.Items.Insert(0, "--Select--");
        conn.Close();
    }
    private void FillAccounts_ClientTo(int client_bankID, int clientID1)
    {
        List<int> client_bank_ID = new List<int>();
        conn.Open();
        SqlCommand sel_client_bank_ID =
            new SqlCommand("SELECT id FROM company_bank WHERE id_bank=@bank_id AND id_company=@company_id", conn);
        sel_client_bank_ID.Parameters.Add(new SqlParameter("@bank_id", System.Data.SqlDbType.Int)).Value = client_bankID;
        sel_client_bank_ID.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = clientID1;
        SqlDataReader rd = sel_client_bank_ID.ExecuteReader();
        while (rd.Read())
        {
            client_bank_ID.Add(Convert.ToInt32(rd["id"]));
        }
        conn.Close();
        foreach (var cl_bank_ID in client_bank_ID)
        {
            conn.Open();
            SqlCommand sel_client_to_bank_account =
                new SqlCommand(
                    "SELECT id_account_number, account_number FROM account_number WHERE id_company_bank=@company_bank_id ",
                    conn);
            sel_client_to_bank_account.Parameters.Add(new SqlParameter("@company_bank_id", System.Data.SqlDbType.Int)).
                Value = cl_bank_ID;
            SqlDataReader rd1 = sel_client_to_bank_account.ExecuteReader();
            while (rd1.Read())
            {
                ListItem item = new ListItem();
                item.Value = rd1["id_account_number"].ToString();
                item.Text = rd1["account_number"].ToString();
                Drop_ACCOUNT_client_to.Items.Add(item);
            }
            conn.Close();
        }
        Drop_ACCOUNT_client_to.Items.Insert(0, "--Select--");
    }

    private List<int> number;
    protected void Drop_doc_type_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (Drop_doc_type.SelectedItem.ToString() != "--Select--")
        {
            string doc;
            string doc_type = Drop_doc_type.SelectedItem.ToString();
            if (doc_type == "Priekšapmaksas rēķins")
            {
                doc = doc_type.Substring(0, 2);
            }
            else
            {
                doc = doc_type.Substring(0, 1);
            }

            number = new List<int>();

            SqlCommand sCmd_doc_num =new SqlCommand("SELECT document_number FROM document WHERE document_number LIKE '" + doc.ToUpper() +"%' AND id_document_type=@document_type_id;", conn);
            sCmd_doc_num.Parameters.Add(new SqlParameter("@document_type_id", SqlDbType.Int)).Value = Drop_doc_type.SelectedValue;
            conn.Open();
            SqlDataReader dr = sCmd_doc_num.ExecuteReader();
            while (dr.Read())
            {
                string doc_num = dr["document_number"].ToString();
                if (doc_num.StartsWith("PR"))
                {
                    string num = doc_num.Substring(2, doc_num.Length - 7);
                    number.Add(Convert.ToInt32(num));
                }
                else
                {
                    string num = doc_num.Substring(1, doc_num.Length - 6);
                    number.Add(Convert.ToInt32(num));
                }
            }
            dr.Close();
            conn.Close();

            DateTime dt = DateTime.Now;
            string year = dt.Year.ToString();
            if (number.Count == 0)
            {
                number.Add(1);
                int maximum = number.Max();
                if (doc_type == "Priekšapmaksas rēķins")
                {
                    doc = doc_type.Substring(0, 2);
                    string date = year;
                    tb_doc_number.Text = doc.ToUpper() + maximum + "/" + date;
                }
                else
                {
                    doc = doc_type.Substring(0, 1);
                    string date = year;
                    tb_doc_number.Text = doc.ToUpper() + maximum + "/" + date;
                }
                number.Clear();
            }
            else
            {
                int maximum = number.Max();
                if (doc_type == "Priekšapmaksas rēķins")
                {
                    doc = doc_type.Substring(0, 2);
                    string date = year;
                    maximum++;
                    tb_doc_number.Text = doc.ToUpper() + maximum + "/" + date;
                }
                else
                {
                    doc = doc_type.Substring(0, 1);
                    string date = year;
                    maximum++;
                    tb_doc_number.Text = doc.ToUpper() + maximum + "/" + date;
                }
            }
        }
        else
        {
            tb_doc_number.Text = "";
        }
    }
}
